Excel BI - Excel Challenge 646

excel-challenges
excel-formulas
🔰 Answer Expected Data Value Jan Feb Mar Apr Quarter Total May Jun
Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 646

Challenge Description

🔰 Answer Expected Data Value Jan Feb Mar Apr Quarter Total May Jun

Solutions

library(tidyverse)
library(readxl)

path = "Excel/646 Insert Quarterly Total Line.xlsx"
input = read_excel(path, range = "A2:B14")
test  = read_excel(path, range = "D2:E18")

result = input %>%
  mutate(Quarter = rep(1:4, each = 3)) 
qt = result %>%
  summarise(Data = "Quarter Total", Value = sum(Value), .by = Quarter) %>%
  bind_rows(result) %>%
  arrange(Quarter, grepl("Total", Data)) %>%
  select(-Quarter)

all.equal(qt, test)
# [1] TRUE
  • Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Aggregate or rank the data at the required grouping level.
  • Strengths: The code maps the workbook rule into a compact, reproducible pipeline.
  • Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
  • Gem: The elegant part is how little code is needed once the correct intermediate representation is chosen.
import pandas as pd

path = "646 Insert Quarterly Total Line.xlsx"
input = pd.read_excel(path, usecols="A:B", skiprows=1, nrows=12)
test = pd.read_excel(path, usecols="D:E", skiprows=1, nrows=17).rename(columns=lambda x: x.split('.')[0])

def insert_quarterly_totals(df):
    quarters = {'Q1': ['Jan', 'Feb', 'Mar'], 'Q2': ['Apr', 'May', 'Jun'], 
                'Q3': ['Jul', 'Aug', 'Sep'], 'Q4': ['Oct', 'Nov', 'Dec']}
    result = pd.concat([df[df['Data'].isin(months)]._append(
        {'Data': 'Quarter Total', 'Value': df[df['Data'].isin(months)]['Value'].sum()}, ignore_index=True)
        for months in quarters.values()])
    return result.reset_index(drop=True)

input_with_totals = insert_quarterly_totals(input)
print(all(input_with_totals == test)) # True

The Python version keeps the algorithm explicit, which helps when the challenge depends on a greedy or iterative rule.

Difficulty Level

Easy / Medium

The business rule is clear, though the workbook still needs a few transformation steps to reach the expected output.